import xlrd
book = xlrd.open_workbook("student.xls")
sheet = book.sheet_by_index(0)  # 根据下表获取相应的表格

import pymysql
conn = pymysql.connect(
    host='localhost',
    user='root',
    passwd='123456',
    db='db_library',
    port=3306,
    charset='utf8'
)
cur = conn.cursor()

a = 0
b = []
for x in range(sheet.nrows):
    for y in range(sheet.ncols):
        if sheet.cell(x,y).ctype == 3:
            date = xlrd.xldate.xldate_as_datetime(sheet.cell(x,y).value,0).strftime('%Y-%m-%d')
            a += 1
            b.append(str(date))
        else:
            data = sheet.cell(x,y).value
            a += 1
            b.append(str(data))
        if a % 4 == 0:
            w = "INSERT INTO student(sid,sname,sage,sgender) VALUE (%s,%s,%s,%s)"
            cur.execute(w,b)
            conn.commit()
            print(b)
            b.clear()

cur.execute(w)
conn.commit()
conn.close()
cur.close()
# print(xlrd.xldate_as_datetime(sheet.cell(6,2).value,0))
# print(xlrd.xldate_as_tuple(sheet.cell(1,2).value,0))
# print(sheet.cell(6,3).ctype)